'''
python和数据库交互
pymysql 第三方库
'''
import pymysql
import faker


# 获取连接
def my_connect():
    try:
        # 连接数据库
        con = pymysql.connect(host="127.0.0.1",
                              user="root",
                              password="31840",
                              port=3306,
                              database="pysqlproject",
                              charset="utf8")
        print("<UNK>")


        return con
    except Exception as e:
        print("-------连接出错-------",e)

# 执行sql语句
def my_execute(con,sql):
    try:
        # 通过connect获取一个游标对象
        cursor1 = con.cursor()
        # 游标对象可以执行sql语句
        cursor1.execute(sql)
        # 提交
        con.commit()
        # 获取查询结果
        re = cursor1.fetchall()
        print(f"---sql语句:{sql}执行了--")
        return re
    except Exception as e:
        print(f"------执行sql语句{sql}出错--------",e)



# 关闭
def myclose(con):
    try:
        # 关闭连接
        con.close()
    except Exception as e:
        print("-----关闭出错---",e)

# 创建表格
def createtable(sql):
    con= my_connect()
    my_execute(con,sql)
    myclose(con)

# 插入数据
def add_employee(name,salary):
    con = my_connect()
    sql = f"insert into employee(name,salary) values('{name}',{salary})"
    print(sql)
    my_execute(con, sql)
    myclose(con)




def insert_faker():
    con = my_connect()
    # print(data)
# {'job': '中国方言翻译', 'company': '南康信息有限公司', 'ssn': '610725196303028761', 'residence': '江苏省玉英市梁平沈阳路p座 483920', 'current_location': (Decimal('-72.968472'), Decimal('26.999673')), 'blood_group': 'AB+', 'website': ['https://jiemin.cn/'], 'username': 'longtao', 'name': '祁健', 'sex': 'F', 'address': '新疆维吾尔自治区欣县友好叶街P座 918862', 'mail': 'txue@hotmail.com', 'birthdate': datetime.date(1930, 7, 6)}
    for i in range(10):
        fk = faker.Faker('zh_CN')
        data = fk.profile()
        sql = """
        INSERT INTO information 
        (job, company, ssn, residence, current_location, blood_group, website, username, name, sex, address, mail, birthdate)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        # 构造 values 元组，顺序与字段一致
        values = (
            data['job'],
            data['company'],
            data['ssn'],
            data['residence'],
            f"{data['current_location'][0]},{data['current_location'][1]}",  # 经纬度转为字符串
            data['blood_group'],
            ','.join(data['website']),  # 列表转字符串
            data['username'],
            data['name'],
            data['sex'],
            data['address'],
            data['mail'],
            data['birthdate']
        )

        # 执行插入（假设 cursor 已经创建，db 已连接）
        cursor = con.cursor()
        cursor.execute(sql, values)  # ✅ 正确：用 cursor 执行
    con.commit()  # 提交事务
    cursor.close()

# 删除数据
def delete_employee():
    pass


# sql = f"create table employee(id int,name char(10),salary float,department_id int)"
# createtable(sql)

# add_employee("tom",4567)
if __name__ == '__main__':
    # my_connect()
    # myclose(my_execute)
    insert_faker()